var express = require('express')
var router = express.Router()
var multipart = require('connect-multiparty');
var path = require("path")
var fs = require("fs")
const jwt = require('jsonwebtoken');
const dayjs = require('dayjs')
const ExcelJS = require('exceljs');
// 中间件：验证JWT token
const verifyToken = (req, res, next) => {
    const token = req.header('Authorization')?.replace('Bearer ', '');
    if (!token) {
        return res.status(401).json({ msg: '未提供访问令牌' });
    }

    try {
        const decoded = jwt.verify(token, process.env.JWT_SECRET);
        // req中存储用户信息
        req.user = decoded;
        next();
    } catch (error) {
        res.status(401).json({ msg: '无效的访问令牌' });
    }
};

// var app = express();
// // 创建multipart中间件实例
// var multipartMiddleware = multipart();

// // 设置上传目录
// app.use(multipart({ uploadDir: './public' }));

// middleware that is specific to this router

// 前端在用到这个文件中的接口时，就检查是否携带token
router.use(function timeLog(req, res, next) {
    console.log('Time: ', Date.now())
    //特殊页面不检测token
    //方法01：手动排除上传接口不检测token，前端不携带
    //方法02：前端携带token，后端全部子路由全部检查
    // if(req.path == '/upload'){
    //     next();
    // }else {
    //模块路由中检测是否携带了 token
    if (!req.auth) {
        return res.send({
            status: 403,
            message: "缺少token"
        });
    }
    next()
})
//  从db文件中导入的数据库模块
var mysql = require("../db/mysql")
// 申请保修
router.post('/apply', (req, res) => {
    // 先获取用户的请求数据，存到变量“body”中
    var body = req.body
    // 用结构复制把用户请求数据里面的的“mobile, pwd, utype”分别拿出来，方便接下来向数据库中添加数据时使用
    let { mobile, urole, emtype, position, emimf, extime, picture, userId } = body
    console.log(body)
    let now = dayjs(new Date()).format("YYYY-MM-DD HH:mm:ss")
    // 添加数据
    mysql.query('INSERT INTO reapply (mobile,urole,emtype,position,emimf,extime,picture,userId,subtime) VALUES (?,?,?,?,?,?,?,?,?)', [
        mobile, urole, emtype, position, emimf, extime, picture, Number(userId), now
    ], function (err, results) {
        // 响应数据
        if (err) {
            console.log(err)
            res.json({ code: 1, Error: err })
        } else {
            res.json({
                code: 0, data: {
                    mobile: mobile, urole: urole, emtype: emtype,
                    position: position, emimf: emimf, extime: extime, picture: picture
                }
            })
        }
        console.log(err + 'ok了!')
    });
    // res.json({ msg: "申请保修", code: 1, data: [] })
})
// 上传图片--单个图片--路由
// 定义路由处理文件上传请求
router.post('/upload', multipart(), function (req, res) {
    // 获取上传的文件信息
    console.log("aaaa", req.files)

    let one_f = req.files.picture;
    var originalFilename = one_f.originalFilename;
    var file_path = one_f.path;

    // 读取文件内容并保存到新位置
    if (file_path) {
        let date = new Date();
        let new_name = date.getTime() + path.extname(originalFilename);
        let new_file_name = 'public/uploads/' + new_name;

        console.log(new_file_name)

        fs.rename(file_path, new_file_name, function (err) {
            if (err) {
                console.log(err)
                res.json({ status: -1, msg: err.message });
            } else {
                res.json({
                    msg: "文件上传完成", code: 0, data: {
                        fileName: `/uploads/${new_name}`,
                        ext: path.extname(new_file_name)
                    }
                })
            }
        });
    }
});
// 上传图片--多文件SS--路由
// 定义路由处理文件上传请求
router.post('/uploadPics', multipart(), function (req, res) {
    // 获取上传的文件信息
    console.log("aaaa", req.files)
    //多图，返回文件数组
    // req.files = {
    //     picture: [
    //       {
    //         fieldName: 'picture',
    //         originalFilename: 'qq_pic_merged_1709551386063.jpg',
    //         path: 'public\\uploads\\sBYh8VnoAIYKrcc62CYLNorX.jpg',
    //         headers: [Object],
    //         size: 42859,
    //         name: 'qq_pic_merged_1709551386063.jpg',
    //         type: 'image/jpeg'
    //       },
    //       {
    //         fieldName: 'picture',
    //         originalFilename: '微信.jpg',
    //         path: 'public\\uploads\\c7ZaR999uw2q40c4pj2FqaCP.jpg',
    //         headers: [Object],
    //         size: 45267,
    //         name: '微信.jpg',
    //         type: 'image/jpeg'
    //       }
    //     ]
    // }
    let one_f = req.files.picture;
    console.log('33333', one_f)
    // var originalFilename;
    // var file_path; 
    var arr = [];
    var arr_base = []
    for (let i = 0; i < one_f.length; i++) {
        // originalFilename += one_f[i].originalFilename + ";";
        // file_path += one_f[i].path;
        arr.push(one_f[i].path)
        // console.log('22222',one_f[i].originalFilename)


        console.log('3333', path.parse(arr[i]))
        arr_base.push('public/uploads/' + path.parse(arr[i]).base)

    }
    res.json({ msg: "文件上传完成", code: 0, data: { pics: arr_base } })
    // 读取文件内容并保存到新位置
    //    if(file_path){
    //         let date = new Date();
    //         let new_name = date.getTime() + path.extname(originalFilename);
    //         let new_file_name = './public/uploads/'+new_name;

    //         console.log(originalFilename,'0000',new_name,new_file_name)

    //         fs.rename(file_path, new_file_name, function(err) {
    //             if (err) {
    //                 console.log(err)
    //                 res.json({status:-1,msg:err.message});
    //             } else {
    //                 res.json({ msg: "文件上传完成", code: 0, data: {
    //                     fileName:new_file_name,
    //                     ext:path.extname(new_file_name)
    //                 }})
    //             }
    //         });
    //    }
});
// 保修详情
router.post('/details', (req, res) => {
    // 先获取用户的请求数据，存到变量“body”中
    var body = req.body
    // 用结构复制把用户请求数据里面的的“mobile, pwd, utype”分别拿出来，方便接下来向数据库中添加数据时使用
    let { mobile, urole, emtype, position, emimf, extime, picture, state, subtime } = body
    // 添加数据
    mysql.query('INSERT INTO reapply (mobile,urole,emtype,position,emimf,extime,picture,state,subtime) VALUES (?,?,?,?,?,?,?,?,?)', [
        mobile, urole, emtype, position, emimf, extime, picture, state, subtime
    ], function (err, results) {
        // 响应数据
        if (err) {
            res.json({ code: 1, msg: '查询错误' })
            console.log(err)
        } else {
            res.json({
                code: 0, mobile: mobile, data: {
                    urole: urole, emtype: emtype, position: position,
                    emimf: emimf, extime: extime, picture: picture, state: state, subtime: subtime
                }
            })
        }
        // console.log(err + 'ok了!')
    });
    // res.json({ msg: "保修详情", code: 2, data: [] })
})
// 报修列表--普通人
router.get('/list', (req, res) => {
    try {
        // 参数处理与验证
        let { page = 1, page_size = 10, userId, emimf, sort, workerId, urole, emtype, position, mobile } = req.query;

        page = parseInt(page);
        page_size = parseInt(page_size);
        if (isNaN(page) || isNaN(page_size) || page < 1 || page_size < 1) {
            return res.status(400).json({ code: 400, msg: "分页参数不合法" });
        }

        const start = (page - 1) * page_size;

        // 基础SQL查询
        let baseSQL = `
            SELECT 
                a.id, a.extime, a.emimf, a.emtype, a.position, 
                a.mobile, a.status, a.toworker, a.noreason, a.picture, a.isUrgent, a.isrevoke, a.ispass,
                submitter.uname AS submitter_name,
                submitter.utype AS submitter_role,
                handler.uname AS handler_name,
                c.pname AS department_name
            FROM reapply a
            LEFT JOIN users submitter ON a.userId = submitter.id
            LEFT JOIN users handler ON a.toworker = handler.id
            LEFT JOIN department c ON handler.utype = c.id
        `;

        // 参数化查询防止SQL注入
        let queryParams = [];
        let whereClauses = ["a.isrevoke = 'false'"];

        if (workerId) {
            whereClauses.push('a.toworker = ?');
            queryParams.push(workerId);
        }

        if (userId) {
            whereClauses.push('a.userId = ?');
            queryParams.push(userId);
        }

        if (emtype) {
            whereClauses.push('a.emtype = ?');
            queryParams.push(emtype);
        }

        if (urole) {
            whereClauses.push('submitter.utype = ?');
            queryParams.push(urole);
        }

        if (mobile) {
            whereClauses.push('(a.mobile LIKE ?)');
            queryParams.push(`%${mobile}%`);
        }

        if (emimf) {
            whereClauses.push('(a.emimf LIKE ?)');
            queryParams.push(`%${emimf}%`);
        }

        if (position) {
            whereClauses.push('(a.position LIKE ?)');
            queryParams.push(`%${position}%`);
        }

        if (sort) {
            whereClauses.push('(a.status = ?)');
            queryParams.push(sort);
        }

        // 组合WHERE子句
        const whereClause = whereClauses.length > 0 ? ` WHERE ${whereClauses.join(' AND ')}` : '';

        // 主查询
        const searchSQL = `${baseSQL}${whereClause} ORDER BY a.id DESC LIMIT ?, ?`;
        queryParams.push(start, page_size);

        // 执行查询
        mysql.query(searchSQL, queryParams, (error, results) => {
            if (error) {
                console.error('查询报修列表出错:', error);
                return res.status(500).json({ code: 500, msg: "服务器内部错误" });
            }

            // 处理结果
            const list = results.map(item => ({
                id: item.id,
                extime: item.extime,
                type: item.emtype,
                imf: item.emimf,
                position: item.position,
                mobile: item.mobile,
                status: item.status,
                worker: item.handler_name,
                workerId: item.toworker,
                reason: item.noreason,
                repairetype: item.emtype,
                uname: item.submitter_name,
                submitter_role: item.submitter_role,
                department: item.department_name,
                picture: item.picture,
                isUrgent: item.isUrgent,
                ispass: item.ispass
            }));

            // 总数查询（包含与主查询相同的JOIN条件）
            const countSQL = `
                SELECT COUNT(*) as total 
                FROM reapply a
                LEFT JOIN users submitter ON a.userId = submitter.id
                LEFT JOIN users handler ON a.toworker = handler.id
                ${whereClause}
            `;
            const countParams = queryParams.slice(0, -2);

            mysql.query(countSQL, countParams, (err, countData) => {
                if (err) {
                    console.error('查询总数出错:', err);
                    return res.status(500).json({ code: 500, msg: "服务器内部错误" });
                }

                const totalNum = countData[0]?.total || 0;

                res.json({
                    code: 0,
                    msg: "success",
                    data: {
                        list,
                        pagination: {
                            current: page,
                            pageSize: page_size,
                            total: totalNum,
                            pages: Math.ceil(totalNum / page_size)
                        }
                    }
                });
            });
        });
    } catch (err) {
        console.error('处理报修列表异常:', err);
        res.status(500).json({ code: 500, msg: "服务器内部错误" });
    }
});
// 报修列表--工人人
router.get('/list_worker', (req, res) => {
    try {
        // 参数处理与验证
        let { page = 1, page_size = 10, keyword, sort, workerId, mobile } = req.query;

        page = parseInt(page);
        page_size = parseInt(page_size);
        if (isNaN(page) || isNaN(page_size) || page < 1 || page_size < 1) {
            return res.status(400).json({ code: 400, msg: "分页参数不合法" });
        }

        const start = (page - 1) * page_size;

        // 基础SQL查询（明确区分 submitter 和 handler）
        let baseSQL = `
            SELECT 
                a.id, a.extime, a.emimf, a.emtype, a.position, 
                a.mobile, a.status, a.toworker, a.noreason, a.picture, a.isUrgent, a.isrevoke, a.ispass,
                submitter.uname AS submitter_name,  -- 报修人姓名
                handler.uname AS handler_name,      -- 处理人姓名
                c.pname AS department_name
            FROM reapply a
            LEFT JOIN users submitter ON a.userId = submitter.id  -- 关联提交人
            LEFT JOIN users handler ON a.toworker = handler.id     -- 关联处理人
            LEFT JOIN department c ON handler.utype = c.id
        `;

        // 参数化查询防止SQL注入
        let queryParams = [];
        let whereClauses = ["a.isrevoke = 'false'"];

        if (workerId) {
            whereClauses.push('a.toworker = ?');
            queryParams.push(workerId);
        }
        if (mobile) {
            whereClauses.push('(a.mobile LIKE ?)');
            queryParams.push(mobile);
        }

        // 添加关键字搜索条件
        if (keyword) {
            whereClauses.push('(a.emimf LIKE ?)');
            const likeKeyword = `%${keyword}%`;
            queryParams.push(likeKeyword);
        }

        // 添加报修的种类
        if (sort) {
            whereClauses.push('(a.status = ?)')
            queryParams.push(sort)
        }


        // 组合WHERE子句
        const whereClause = whereClauses.length > 0 ? ` WHERE ${whereClauses.join(' AND ')}` : '';

        // 主查询
        const searchSQL = `${baseSQL}${whereClause} ORDER BY a.id DESC LIMIT ?, ?`;
        queryParams.push(start, page_size);

        // 执行查询
        mysql.query(searchSQL, queryParams, (error, results) => {
            if (error) {
                console.error('查询报修列表出错:', error);
                return res.status(500).json({ code: 500, msg: "服务器内部错误" });
            }

            // 处理结果
            const list = results.map(item => ({
                id: item.id,
                extime: item.extime,
                type: item.emtype,
                imf: item.emimf,
                position: item.position,
                mobile: item.mobile,
                status: item.status,
                worker: item.handler_name,  // 使用处理人姓名
                workerId: item.toworker,
                reason: item.noreason,
                repairetype: item.emtype,
                uname: item.submitter_name, // 使用提交人姓名
                department: item.department_name,
                picture: item.picture,  // 添加图片字段
                isUrgent: item.isUrgent,
                ispass: item.ispass
            }));

            // 总数查询（条件与主查询一致）
            const countSQL = `SELECT COUNT(*) as total FROM reapply a${whereClause}`;
            const countParams = queryParams.slice(0, -2); // 移除LIMIT参数

            mysql.query(countSQL, countParams, (err, countData) => {
                if (err) {
                    console.error('查询总数出错:', err);
                    return res.status(500).json({ code: 500, msg: "服务器内部错误" });
                }

                const totalNum = countData[0]?.total || 0;

                res.json({
                    code: 0,
                    msg: "success",
                    data: {
                        list,
                        pagination: {
                            current: page,
                            pageSize: page_size,
                            total: totalNum,
                            pages: Math.ceil(totalNum / page_size)
                        }
                    }
                });
            });
        });
    } catch (err) {
        console.error('处理报修列表异常:', err);
        res.status(500).json({ code: 500, msg: "服务器内部错误" });
    }
});

// 报修加急
router.post("/urgent/:id", verifyToken, (req, res) => {
    let id = parseInt(req.params.id);
    // if(req.user.id !== id){
    //     res.status(400).json({
    //         code:-1,
    //         msg: "只能给自己的报修加急"
    //     })
    // }
    mysql.query("UPDATE reapply SET isUrgent = 'true' WHERE id = ?", [id], (error, results) => {
        if (error) {
            console.log(error, error.sql)
            res.status(500).json({ code: -1, msg: "后端报错" })
        } else {
            res.json({ code: 0, msg: "状态修改完毕！" })
        }
    })
})

// 报修撤销
router.post("/revoke/:id", verifyToken, (req, res) => {
    let id = parseInt(req.params.id);
    // if(req.user.id !== id){
    //     res.status(400).json({
    //         code:-1,
    //         msg: "只能给自己的报修加急"
    //     })
    // }
    mysql.query("UPDATE reapply SET isrevoke = 'true' WHERE id = ?", [id], (error, results) => {
        if (error) {
            console.log(error, error.sql)
            res.status(500).json({ code: -1, msg: "后端报错" })
        } else {
            res.json({ code: 0, msg: "报修撤销完毕！" })
        }
    })
})

// 报修是否通过
router.post("/isPass/:id", verifyToken, (req, res) => {
    let id = parseInt(req.params.id);
    // if(req.user.id !== id){
    //     res.status(400).json({
    //         code:-1,
    //         msg: "只能给自己的报修加急"
    //     })
    // }
    const { value } = req.body
    mysql.query("UPDATE reapply SET ispass = ?,status = 0 WHERE id = ?", [value, id], (error, results) => {
        if (error) {
            console.log(error, error.sql)
            res.status(500).json({ code: -1, msg: "后端报错" })
        } else {
            res.json({ code: 0, msg: "报修处理完毕！" })
        }
    })
})

// 报修不通过原因
router.post("/noreason/:id", verifyToken, (req, res) => {
    let id = parseInt(req.params.id);
    // if(req.user.id !== id){
    //     res.status(400).json({
    //         code:-1,
    //         msg: "只能给自己的报修加急"
    //     })
    // }
    const { reason } = req.body
    mysql.query("UPDATE reapply SET noreason = ? WHERE id = ?", [reason, id], (error, results) => {
        if (error) {
            console.log(error, error.sql)
            res.status(500).json({ code: -1, msg: "后端报错" })
        } else {
            res.json({ code: 0, msg: "报修处理完毕！" })
        }
    })
})

// 指定工人
router.get('/toworker/:id', verifyToken, (req, res) => {
    let id = parseInt(req.params.id);
    let { workerid } = req.query;
    if (req.user.id == workerid) {
        return res.status(400).json({
            code: -1,
            msg: "不能自己指定自己"
        })
    }
    mysql.query("UPDATE reapply SET toworker = ? WHERE id = ?", [workerid, id], function (error, results) {
        // /baoxiu/toworker/8?workerid=26

        // { workerid: '26' } /toworker/8 中的8是动态路由参数，在params中
        // { id: '8' } ?workerid=26 ?后面的，叫查询参数，是在query中
        console.log(req.query, req.params, error)

        if (error) {
            console.log(error, error.sql); //error.sql 获取sql语句
            // 可以选择发送错误信息给客户端
            res.json({ msg: "更新失败", code: 1 });
        } else {
            // 更新成功
            res.json({ msg: "指定完毕！", code: 0 });
        }
    });
})

// 导出Excel文件接口
router.post("/export-excel", (req, res) => {
    // 参数校验
    if (!req.body.data || !Array.isArray(req.body.data)) {
        return res.status(400).json({
            code: -1,
            msg: "参数错误：需要传递data数组"
        });
    }

    const rows = req.body.data;
    const workbook = new ExcelJS.Workbook();

    try {
        const sheet = workbook.addWorksheet('Sheet1');

        // 动态提取表头（安全处理空数组情况）
        const headers = rows.length > 0 ? Object.keys(rows[0]) : [];
        if (headers.length > 0) {
            sheet.addRow(headers);

            // 添加数据
            rows.forEach(row => {
                const values = headers.map(header => row[header] ?? ''); // 处理可能的undefined
                sheet.addRow(values);
            });
        }

        // 设置响应头
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', 'attachment; filename=export.xlsx');

        // 返回文件流
        workbook.xlsx.write(res)
            .then(() => res.end())
            .catch(err => {
                console.error('Excel写入失败:', err);
                res.status(500).json({
                    code: -1,
                    msg: "文件生成失败"
                });
            });

    } catch (err) {
        console.error('Excel生成异常:', err);
        res.status(500).json({
            code: -1,
            msg: "服务器内部错误"
        });
    }
});

// 工人列表
router.get("/worker_list", (req, res) => {
    let sql = `
        SELECT id,uname
        FROM users
        WHERE utype = 9
    `
    mysql.query(sql, (error, results) => {
        if (error) {
            console.log(error)
            res.status(500).json({
                code: -1,
                msg: "接口报错了"
            })
        } else {
            res.json({
                code: 0,
                data: results,
                msg: "工人列表请求成功！"
            })
        }
    })
})

// 人员列表
router.get('/people_list', async (req, res) => {
    try {
        // 获取token信息
        console.log('Token信息:', req.auth);

        // 使用更清晰的SQL查询
        const selectSQL = `
            SELECT id, uname, utype AS part
            FROM users
            WHERE status = 1 AND utype = 9 
            ORDER BY id ASC  
        `;

        // 使用Promise封装MySQL查询
        const results = await new Promise((resolve, reject) => {
            mysql.query(selectSQL, (error, results) => {
                if (error) {
                    console.error('数据库查询错误:', error);
                    reject(error);
                } else {
                    resolve(results);
                }
            });
        });

        // 直接映射结果，避免不必要的循环和变量声明
        const data = results.map(user => ({
            id: user.id,
            uname: user.uname,
            part: user.part  // 使用AS重命名的字段
        }));

        // 统一响应格式
        res.json({
            code: 0,
            msg: "获取人员列表成功",
            data,
            timestamp: Date.now()
        });

    } catch (error) {
        console.error('获取人员列表异常:', error);
        res.status(500).json({
            code: 500,
            msg: "服务器内部错误",
            error: error.message,
            timestamp: Date.now()
        });
    }
});
// 报修状态
router.post("/status/:id", (req, res) => {
    let id = parseInt(req.params.id);

    let { status } = req.body;
    console.log(status)
    mysql.query('UPDATE reapply SET status=? WHERE id=?', [status, id], function (error, results) {
        if (error) {
            console.log(error, error.sql)
        } else {
            res.json({ code: 0, msg: "状态修改完毕！" })
        }
    })
})
// 保修反馈
router.get('/feedback', (req, res) => {
    mysql.query('SELECT * FROM reapply', function (error, results) {
        // res.json(results)————返回全部数据
        // 相应数据
        // var body = req.body
        var date;
        var type;
        var imf;
        var position;
        var mobile;
        var id;
        var state;
        let arr = [];
        for (let i = 0; i < results.length; i++) {
            date = results[i].subtime
            type = results[i].emtype
            imf = results[i].emimf
            position = results[i].position
            mobile = results[i].mobile
            id = results[i].id
            state = results[i].mainstate
            arr.push({ id, date, type, imf, position, mobile, state })
        }
        res.json({ code: 0, msg: "保修反馈", data: arr })
    })
    // res.json({ msg: "保修反馈", code: 2, data: [] })
})
// 报修申请删除--单个
router.get('/delete/:id', (req, res) => {
    console.log(req.params.id)
    mysql.query('DELETE FROM reapply WHERE id = ?', [parseInt(req.params.id)],
        function (error, result) {
            if (error) {
                res.json({ code: 1, msg: "删除失败", data: { err } })
            } else {
                res.json({ code: 0, msg: "删除成功" })
            }
        }
    )
})
// 多个用户类型的删除、
router.get('/deletes/:id', (req, res) => {
    console.log(req.params.id)
    let userids = req.params.id
    let useridps = JSON.parse(userids)
    let tiaojian = 'DELETE FROM reapply WHERE id IN (?)';
    mysql.query(tiaojian, [useridps], function (error, result) {
        if (error) {
            res.json({ code: 1, msg: "删除失败", data: { error: error } });
        } else {
            res.json({ code: 0, msg: "删除成功", data: { affectedRows: result.affectedRows } });
        }
    });
})
// 报修修改 : 获取信息
router.get('/edit/:id', (req, res) => {
    console.log(req.query, req.params)
    // 、！！！ get 取参数
    //  ?page=1&page_size=3,参数放在请求字符客户，问号开头的，req.query中 { page: '1', page_size: '3' }取
    //  /renyuan/edit/5 ,参数是动态路由 （/edit/:id），req.param中取参数 { id: '5' } 取
    if (!req.params.id) {
        res.send({ code: 1, msg: '缺少参数', data: [] });
    }
    let selectSQL = `SELECT * FROM reapply WHERE id = ?`
    mysql.query(selectSQL, [parseInt(req.params.id)], function (error, results) {
        console.log(error, results[0].userId)
        if (error) {
            console.log(error)
            res.status(500).json({
                code: -1,
                msg: "后端报错"
            })
        } else {
            let userInfo = `
                SELECT users.id,users.account,users.uname,users.pfspeciality,users.utype
                FROM users
                WHERE id = ?
            `
            mysql.query(userInfo, [results[0].userId], (error, results2) => {
                if (error) {
                    console.log(error)
                    res.status(500).json({
                        code: -1,
                        msg: "后端报错"
                    })
                } else {
                    res.json({ code: 0, msg: "信息详情", data: results[0], userInfo: results2[0] })
                }
            })

        }

    })
})
// 报修修改 : 保存信息
router.post('/save_edit/:id', (req, res) => {
    // 声明body
    let id = parseInt(req.params.id); //动态路由中的参数通过params取
    let { mobile, emtype, urole, position, emimf, extime } = req.body; //普通提交的数据，在req.body中取到
    // 执行sql语句
    mysql.query('UPDATE reapply SET emimf=?,position=?,extime=?,mobile=?,emtype=?,urole=? WHERE id=?',
        [emimf, position, extime, mobile, emtype, urole, id], function (error, results) {
            if (error) console.log('edit error:', error)
            // 响应信息
            res.json({ msg: "修改完毕", code: 0, })
        })
    // res.json({ msg: "人员修改", code: 1, data: [] })
})

// 报修搜索--模糊查询
router.get("/search", (req, res) => {

    console.log(req.query);
    let { mobile, urole, emtype, position, emimf, status, page, page_size } = req.query;
    // 查询limit
    let start = (page - 1) * page_size;
    let limit = ` limit ${start}, ${page_size}` //limit 0,3

    // 根据搜索的内容不同，生成查询条件
    let whereConditions = [];
    let params = [];

    if (mobile) {
        whereConditions.push(`a.mobile LIKE ?`);
        params.push(`%${mobile}%`);
    }

    if (position) {
        whereConditions.push(`a.position LIKE ?`);
        params.push(`%${position}%`);
    }

    if (emimf) {
        whereConditions.push(`a.emimf LIKE ?`);
        params.push(`%${emimf}%`);
    }

    if (urole) {
        whereConditions.push(`a.urole = ?`);
        params.push(Number(urole));
    }

    if (emtype) {
        whereConditions.push(`a.emtype = ?`);
        params.push(emtype);
    }

    if (status) {
        whereConditions.push(`a.status = ?`);
        params.push(status);
    }
    let where = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // SQL查询语句
    let searchSQL = `
    SELECT a.id, a.extime, a.emimf, a.emtype, a.urole, a.position, a.mobile, a.status, a.toworker, a.noreason, b.uname, b.utype, c.pname
    FROM reapply AS a
    LEFT JOIN users AS b ON a.toworker = b.id
    LEFT JOIN department AS c ON b.utype = c.id
    ${where} ${limit}`;

    // 计算总数的SQL查询语句
    let countSQL = `
    SELECT COUNT(*)
    FROM reapply AS a
    LEFT JOIN users AS b ON a.toworker = b.id
    LEFT JOIN department AS c ON b.utype = c.id
    ${where}`;

    mysql.query(countSQL, params, function (error, countResults) {
        if (error) {
            console.error(error);
            return res.status(500).json({ code: 1, msg: "数据库查询错误", error: error.message });
        }
        let total = countResults[0] ? countResults[0]['COUNT(*)'] : 0;

        mysql.query(searchSQL, params, function (error, results) {
            if (error) {
                console.error(error);
                return res.status(500).json({ code: 1, msg: "数据库查询错误", error: error.message });
            }

            let arr = results.map(row => ({
                id: row.id,
                extime: row.extime,
                type: row.urole,
                imf: row.emimf,
                position: row.position,
                mobile: row.mobile,
                repairetype: row.emtype,
                status: row.status,
                worker: row.toworker,
                reason: row.noreason,
                uname: row.uname,
                product_name: row.pname // 确保这个字段在数据库查询中被包含
            }));

            console.log(arr);
            res.json({
                code: 0,
                msg: "报修搜索",
                data: arr,
                total: total // 包含总数的数据
            });
        });
    });
});

// 总数量
router.get("/total_number", (req, res) => {
    /*
    <el-option label="待审核" value="0"></el-option>
    <el-option label="通过" value="1"></el-option>
    <el-option label="处理中" value="2"></el-option>
    <el-option label="完成" value="3"></el-option>
    <el-option label="撤销" value="-1"></el-option>
    <el-option label="不通过" value="-2"></el-option>
    */
    var totalNum_wait;
    var totalNum_ing;
    var totalNum_ok;
    var totalNum_users;
    var totalNum_invents = 0;
    var totalNum_parts;

    // 使用Promise来处理异步查询
    function queryDB(query, callback) {
        mysql.query(query, function (error, results) {
            if (error) {
                console.log(error);
            } else {
                callback(results[0]?.total);
            }
        });
    }
    // 使用async/await来处理异步操作
    async function getTotalNumbers() {
        await queryDB("SELECT count(id) as total FROM reapply WHERE status = 0", (total) => {
            totalNum_wait = total;
        });
        await queryDB("SELECT count(id) as total FROM reapply WHERE status = 2", (total) => {
            totalNum_ing = total;
        });
        await queryDB("SELECT count(id) as total FROM reapply WHERE status = 3", (total) => {
            totalNum_ok = total;
        });
        await queryDB("SELECT count(id) as total FROM users", (total) => {
            totalNum_users = total;
        });
        await queryDB("SELECT count(id) as total FROM department", (total) => {
            totalNum_parts = total;
        });

        const inventoryResults = await new Promise((resolve, reject) => {
            mysql.query("SELECT newnumber FROM inventory", function (error, results) {
                if (error) {
                    console.log(error);
                    reject(error);
                } else {
                    let total = 0;
                    for (let i = 0; i < results.length; i++) {
                        total += parseInt(results[i].newnumber, 10) || 0;
                    }
                    totalNum_invents = total;
                    resolve(totalNum_invents);
                }
            });
        });
        const arr = [totalNum_wait, totalNum_ing, totalNum_ok, totalNum_users, totalNum_invents, totalNum_parts];
        return arr;
    }
    getTotalNumbers().then((arr) => {
        res.json({ code: 0, msg: "总数信息", data: arr });
    }).catch((error) => {
        console.log(error);
        res.json({ code: 1, msg: "查询失败", data: null });
    });
})
// await 是更好的异步解决方案，更简洁。不需要用then...catch,await等待的是Promise对象里面then的结果。
// 用到await的函数需要async function(){}来标记。
router.get("/status_number", async function (req, res) {
    var totalNum_wait;
    var totalNum_pass;
    var totalNum_ing;
    var totalNum_ok;
    var totalNum_revoke;
    var totalNum_no;
    var arr = [];
    //方法3：Promise 原始用法+优化+async await
    const getTotalNum = (status) => {
        return new Promise((resolve, reject) => {
            return mysql.query(`SELECT count(id) AS total FROM reapply WHERE status = ${status}`, (error, data) => {
                if (error) {
                    reject(error)
                    console.log(error.sql);
                } else {
                    resolve(data[0]?.total)
                    // arr.push(totalNum_pass);
                }
            });
        })
    }
    // 错误处理，使用try...catch
    try {
        // 成功
        let num01 = await getTotalNum(0)
        let num02 = await getTotalNum(1)
        let num03 = await getTotalNum(2)
        let num04 = await getTotalNum(3)
        let num05 = await getTotalNum(-1)
        let num06 = await getTotalNum(-2)
        res.json({ code: 0, msg: "状态条数", data: [num01, num02, num03, num04, num05, num06] });
    }
    catch {
        // 失败
        res.json({ code: -1, msg: "状态条数", data: [] });
    }

})
router.get("/repaire_type", function (req, res) {
    var arr = [];
    const p1 = new Promise(function (resolve, reject) {
        mysql.query("SELECT count(id) AS total FROM reapply WHERE emtype = 1", (error, data) => {
            if (error) {
                console.log(error)
            } else {
                resolve(data[0]?.total)
            }
        })
    })
    const p2 = new Promise(function (resolve, reject) {
        mysql.query("SELECT count(id) AS total FROM reapply WHERE emtype = 2", (error, data) => {
            if (error) {
                console.log(error)
            } else {
                resolve(data[0]?.total)
            }
        })
    })
    Promise.all([p1, p2])
        .then((data) => {
            arr.push(data[0], data[1])
            res.json({ code: 0, msg: "报修类型条数", data: arr });
        })
        .catch((error) => {
            console.log(error)
        })
})
// 维修师傅的--每个人完成多少任务
router.get("/orders_num", (req, res) => {
    let MySql = `
        SELECT COUNT(a.id)as task_total,b.account,b.uname,b.id
        FROM reapply as a
        LEFT JOIN users as b on a.toworker = b.id
        WHERE a.toworker !=""
        GROUP BY a.toworker
    `
    // GROUP BY a.toworker  按师傅筛选分组查询完成的维修任务总数，分组查询,就是按某个类别统计数据
    // Having task_total>=3 按师傅筛选，维修次数大于等于3的师傅，Having 配合GROUP BY 一起使用，对已经查询到的结果进行筛选
    mysql.query(MySql, function (error, results) {
        if (error) {
            console.log(error)
        } else {
            console.log(results)
            res.json({ msg: "修改完毕", code: 0, data: results })
        }
    })
})
// // 其他-班级留言板接口-列表
// router.get("/class_list",(req,res)=>{
//     mysql.query("SELECT id,content FROM ClassMessage",function(error,results){
//         if(error){
//             console.log(error)
//         }else{
//             res.json({ code: 0, msg: "报修列表", data: results})
//         }
//     })
// })
// // 其他-班级留言板接口-删除
// router.get("/class_del/:id",(req,res)=>{
//     mysql.query('DELETE FROM ClassMessage WHERE id = ?', [parseInt(req.params.id)],
//         function (error, result) {
//             if (error) {
//                 res.json({ code: 1, msg: "删除失败", data: { err } })
//             } else {
//                 res.json({ code: 0, msg: "删除成功" })
//             }
//         }
//     )
// })
// // 其他-班级留言板接口-添加
// router.post("/class_add",(req,res)=>{
//     var body = req.body
//     let { content } = body
//     mysql.query('INSERT INTO ClassMessage (content) VALUES (?)', content,
//         function (error, result) {
//             if (error) {
//                 console.log(error.sql)
//                 res.json({ code: 1, msg: "添加失败", data: { error } })
//             } else {
//                 res.json({ code: 0, msg: "添加成功" ,data : result})
//             }
//         }
//     )
// })
module.exports = router;